/*******************************************************************************
 *
 * DSM - Distributed Search Manager
 * Developed by Milspec Research International Pty Ltd
 *
 * $Author: bslattery $
 * $Revision: 1.4 $
 * $Date: 2007/09/13 04:08:45 $
 *
 * (c)Copyright 2004
 *    education.au limited
 *    DEST
 *    All rights reserved.
 *
 * Redistribution and use in source and binary forms, with or without
 * modification, are permitted provided that the following conditions are met:
 *
 * Redistributions of source code must retain the above copyright notice, this
 * list of conditions and the following disclaimer.
 *
 * Redistributions in binary form must reproduce the above copyright notice,
 * this list of conditions and the following disclaimer in the documentation
 * and/or other materials provided with the distribution.
 *
 * Neither the names education.au limited, DEST nor the names of its
 * contributors may be used to endorse or promote products derived from this
 * software without specific prior written permission.
 *
 * To the extent permitted by law, the copyright owners of this software and
 * its contributors:
 *
 *  (i) exclude all warranties in relation to the software; and
 * (ii) exclude liability for all losses, costs, expenses and damages arising
 *      in any way from the use of the software whether arising from or in
 *      relation to breach of contract, negligence or any other tort, in equity
 *      or otherwise. If the software is in breach of a warranty which is
 *      implied by law, the copyright owners and contributors liability is
 *      limited to the replacement of the software.
 *
 *******************************************************************************
 */

package au.edu.educationau.opensource.dsm.service;

import java.io.BufferedReader;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.Enumeration;
import java.util.Hashtable;
import java.util.Properties;
import java.util.StringTokenizer;
import java.util.Vector;

import org.apache.log4j.Logger;

/**
 * This class is a Singleton that provides access to one or many connection
 * pools defined in the db.properties file. A client gets access to the single
 * instance through the static getInstance() method and can then check-out and
 * check-in connections from a pool. When the client shuts down it should call
 * the release() method to close all open connections and do other clean up.
 * 
 */

public class DBConnectionService {
	static Logger logger = Logger.getLogger(DBConnectionService.class.getName());

	/** Singleton instance */
	static private DBConnectionService instance;
	static private int clients;

	private Vector drivers = new Vector();
	private Hashtable pools = new Hashtable();

	/**
	 * Returns the single instance, creating one if it's the first time this
	 * method is called.
	 * 
	 * @return DBConnectionService The single instance.
	 */
	static synchronized public DBConnectionService getInstance() {
		return getInstance(null);
	}

	static synchronized public DBConnectionService getInstance(String propFile) {
		if (instance == null) {
			logger.info("Starting Database Connection Pool Service...");
			instance = new DBConnectionService(propFile);
			logger.info("Database Connection Pool Service Started OK!");
		}
		clients++;
		return instance;
	}

	/**
	 * A private constructor since this is a Singleton
	 */
	private DBConnectionService(String propFile) {
		init(propFile);
	}

	/**
	 * Returns a connection to the named pool.
	 * 
	 * @param name
	 *            The pool name as defined in the properties file
	 * @param con
	 *            The Connection
	 */
	public void freeConnection(String name, Connection con) {
		DBConnectionPool pool = (DBConnectionPool) pools.get(name);
		if (pool != null) {
			pool.freeConnection(con);
		}
	}

	/**
	 * Returns an open connection. If no one is available, and the max number of
	 * connections has not been reached, a new connection is created.
	 * 
	 * @param name
	 *            The pool name as defined in the properties file
	 * @return Connection The connection or null
	 * @exception Exception
	 */
	public Connection getConnection(String name) throws Exception {
		DBConnectionPool pool = (DBConnectionPool) pools.get(name);
		if (pool != null) {
			return pool.getConnection();
		} else {
			logger.error("Pool " + name + " is null.", null);
		}
		return null;
	}

	/**
	 * Returns an open connection. If no one is available, and the max number of
	 * connections has not been reached, a new connection is created. If the max
	 * number has been reached, waits until one is available or the specified
	 * time has elapsed.
	 * 
	 * @param name
	 *            The pool name as defined in the properties file
	 * @param time
	 *            The number of milliseconds to wait
	 * @return Connection The connection or null
	 */
	public Connection getConnection(String name, long time) {
		DBConnectionPool pool = (DBConnectionPool) pools.get(name);
		if (pool != null) {
			return pool.getConnection(time);
		}
		return null;
	}

	/**
	 * Closes all open connections and deregisters all drivers.
	 */
	public synchronized void release() {
		// Wait until called by the last client
		if (--clients != 0) {
			return;
		}

		Enumeration allPools = pools.elements();
		while (allPools.hasMoreElements()) {
			DBConnectionPool pool = (DBConnectionPool) allPools.nextElement();
			pool.release();
		}
		Enumeration allDrivers = drivers.elements();
		while (allDrivers.hasMoreElements()) {
			Driver driver = (Driver) allDrivers.nextElement();
			try {
				DriverManager.deregisterDriver(driver);
				logger.debug("Deregistered JDBC driver " + driver.getClass().getName());
			} catch (SQLException e) {
				logger.error("Can't deregister JDBC driver: " + driver.getClass().getName(), e);
			}
		}
	}

	/**
	 * Closes all open connections and deregisters all drivers.
	 */
	public synchronized String dumpDBUsage() {
		StringBuffer logDump = new StringBuffer();
		Enumeration allPools = pools.elements();
		while (allPools.hasMoreElements()) {
			DBConnectionPool pool = (DBConnectionPool) allPools.nextElement();
			if (null != pool) {
				logDump.append("[Pool: ");
				logDump.append(pool.getName());
				logDump.append("]");
				logDump.append(" [MaxConn=");
				logDump.append(pool.getMaxConn());
				logDump.append("]");
				logDump.append(" [FreeConn=");
				logDump.append(pool.getFreeConnections());
				logDump.append("]");
				logDump.append(" [Counter=");
				logDump.append(pool.getCounter());
				logDump.append("]");
			} else {
				logger.info("[Pool: null]");
			}
		}
		logger.info(logDump.toString());
		return logDump.toString();
	}

	/**
	 * Creates instances of DBConnectionPool based on the properties. A
	 * DBConnectionPool can be defined with the following properties:
	 * 
	 * <PRE>
	 * &lt;poolname&gt;.url         The JDBC URL for the database
	 * &lt;poolname&gt;.user        A database user (optional)
	 * &lt;poolname&gt;.password    A database user password (if user specified)
	 * &lt;poolname&gt;.maxconn     The maximal number of connections (optional)
	 * </PRE>
	 * 
	 * @param props
	 *            The connection pool properties
	 */
	private void createPools(Properties props) {
		Enumeration propNames = props.propertyNames();
		while (propNames.hasMoreElements()) {
			String name = (String) propNames.nextElement();
			if (name.endsWith(".url")) {
				String poolName = name.substring(0, name.lastIndexOf("."));
				String url = props.getProperty(poolName + ".url");
				if (url == null) {
					logger.debug("No URL specified for " + poolName);
					continue;
				}
				String user = props.getProperty(poolName + ".user");
				String password = props.getProperty(poolName + ".password");
				String maxconn = props.getProperty(poolName + ".maxconn", "0");
				int max;
				try {
					max = Integer.valueOf(maxconn).intValue();
				} catch (NumberFormatException e) {
					logger.error("Invalid maxconn value " + maxconn + " for " + poolName, e);
					max = 0;
				}
				DBConnectionPool pool = new DBConnectionPool(poolName, url, user, password, max);
				pools.put(poolName, pool);
				logger.debug("Initialized pool " + poolName);
			}
		}
	}

	/**
	 * @param db
	 * @param table
	 * @param col
	 * @exception Exception
	 */
	public synchronized int nextVal(String db, String table, String col) throws Exception {
		int nextval = 1;
		String query = "SELECT max(" + col + ") idx FROM " + table;
		Connection conn = getInstance().getConnection(db);
		try {
			ResultSet rs = conn.createStatement().executeQuery(query);
			while (rs.next()) {
				nextval = rs.getInt("idx") + 1;
			}
			rs.close();
		} catch (Exception o) {
			logger.error("Cannot access DB " + db, o);
		}
		getInstance().freeConnection("db", conn);
		return nextval;
	}

	/**
	 * Loads properties and initializes the instance with its values.
	 */
	private void init(String propertiesFile) {
		Properties dbProps = new Properties();
		try {
			FileReader fReader = new FileReader(propertiesFile);
			BufferedReader reader = new BufferedReader(fReader);
			while (reader.ready()) {
				String propLine = reader.readLine();
				if (propLine.length() > 0 && propLine.indexOf("=") > 0) {
					String propName = propLine.substring(0, propLine.indexOf("=")).trim();
					String propValue = propLine.substring(propLine.indexOf("=") + 1).trim();
					dbProps.setProperty(propName, propValue);
				} // if
			}
			logger.debug("db.properties file read successfully");
		} catch (Exception e) {
			logger.error("Can't read db.properties. Setting DB Usage to UNAVAILABLE.", e);
			return;
		}

		try {
			loadDrivers(dbProps);
			createPools(dbProps);
		} catch (Exception oe) {
			logger.error("Can't load drivers and/or create db connection pool. Setting DB Usage to UNAVAILABLE.", oe);
		}
	}

	/**
	 * Loads and registers all JDBC drivers. This is done by the
	 * DBConnectionService, as opposed to the DBConnectionPool, since many pools
	 * may share the same driver.
	 * 
	 * @param props
	 *            The connection pool properties
	 */
	private void loadDrivers(Properties props) {
		String driverClasses = props.getProperty("drivers");
		StringTokenizer st = new StringTokenizer(driverClasses, " ");
		while (st.hasMoreElements()) {
			String driverClassName = st.nextToken().trim();
			try {
				Driver driver = (Driver) Class.forName(driverClassName).newInstance();
				DriverManager.registerDriver(driver);
				drivers.addElement(driver);
				logger.debug("Registered JDBC driver " + driverClassName);
			} catch (Exception e) {
				logger.error("Can't register JDBC driver: " + driverClassName, e);
			}
		}
	}

	/***/
	public String toString() {
		return "org.milspec.db.DBConnectionService";
	}

	/**
	 * This inner class represents a connection pool. It creates new connections
	 * on demand, up to a max number if specified. It also makes sure a
	 * connection is still open before it is returned to a client.
	 */
	class DBConnectionPool {
		private int counter = 0;
		private Vector freeConnections = new Vector();
		private int maxConn = 0;
		private String name;
		private String password;
		private String URL;
		private String user;

		public String toString() {
			return "org.milspec.db.DBCMan.DBConnectionPool";
		}

		/**
		 * Creates new connection pool.
		 * 
		 * @param name
		 *            The pool name
		 * @param URL
		 *            The JDBC URL for the database
		 * @param user
		 *            The database user, or null
		 * @param password
		 *            The database user password, or null
		 * @param maxConn
		 *            The maximal number of connections, or 0 for no limit
		 */
		public DBConnectionPool(String name, String URL, String user, String password, int maxConn) {
			this.name = name;
			this.URL = URL;
			this.user = user;
			this.password = password;
			this.maxConn = maxConn;

			if (maxConn > 0) {
				freeConnections = new Vector(maxConn);
				for (int i = 0; i < maxConn; i++) {
					freeConnections.add(newConnection());
				}
			}
		}

		public String getName() {
			return name;
		}

		public int getCounter() {
			return counter;
		}

		public int getMaxConn() {
			return maxConn;
		}

		public int getFreeConnections() {
			return freeConnections.size();
		}

		/**
		 * Checks in a connection to the pool. Notify other Threads that may be
		 * waiting for a connection.
		 * 
		 * @param con
		 *            The connection to check in
		 */
		public synchronized void freeConnection(Connection con) {
			// Put the connection at the end of the Vector
			if (null != con && freeConnections.size() < maxConn) {
				freeConnections.add(con);
				notifyAll();
			}
		}

		/**
		 * Checks out a connection from the pool. If no free connection is
		 * available, a new connection is created unless the max number of
		 * connections has been reached. If a free connection has been closed by
		 * the database, it's removed from the pool and this method is called
		 * again recursively.
		 * 
		 * If there are no connections free, an exception is thrown.
		 */
		public synchronized Connection getConnection() {
			Connection con = null;
			if (freeConnections.size() <= 0 && maxConn > 0) {
				throw new RuntimeException("No available database connections, and " + DBConnectionService.class.getName() + " doesn't support queueing requests.");
			} else if (maxConn == 0) {
				return newConnection();
			} else {
				con = (Connection) freeConnections.get(0);
				freeConnections.removeElementAt(0);
				counter++;
			}
			try {
				if (con.isClosed()) {
					if (freeConnections.size() < maxConn) {
						con = newConnection();
					} else {
						con = getConnection();
					}
				}
			} catch (SQLException e) {
				// Try again recursively
				con = getConnection();
			}
			return con;
		}

		/**
		 * Checks out a connection from the pool. If no free connection is
		 * available, a new connection is created unless the max number of
		 * connections has been reached. If a free connection has been closed by
		 * the database, it's removed from the pool and this method is called
		 * again recursively.
		 * <P>
		 * If no connection is available and the max number has been reached,
		 * this method waits the specified time for one to be checked in.
		 * 
		 * @param timeout
		 *            The timeout value in milliseconds
		 */
		public synchronized Connection getConnection(long timeout) {
			long startTime = new Date().getTime();
			Connection con;
			while ((con = getConnection()) == null) {
				try {
					wait(timeout);
				} catch (InterruptedException e) {
				}
				if ((new Date().getTime() - startTime) >= timeout) {
					// Timeout has expired
					return null;
				}
			}
			return con;
		}

		/**
		 * Closes all available connections.
		 */
		public synchronized void release() {
			Enumeration allConnections = freeConnections.elements();
			while (allConnections.hasMoreElements()) {
				Connection con = (Connection) allConnections.nextElement();
				try {
					con.close();
					logger.debug("Closed connection for pool " + name);
				} catch (SQLException e) {
					logger.error("Can't close connection for pool " + name, e);
				}
			}
			freeConnections.removeAllElements();
		}

		/**
		 * Creates a new connection, using a userid and password if specified.
		 */
		private Connection newConnection() {
			Connection con = null;
			try {
				if (user == null) {
					con = DriverManager.getConnection(URL);
				} else {
					con = DriverManager.getConnection(URL, user, password);
				}
				logger.debug("Created a new connection in pool " + name);
			} catch (SQLException e) {
				logger.error("Can't create a new connection for " + URL, e);
				return null;
			}
			return con;
		}
	}
}
